home *** CD-ROM | disk | FTP | other *** search
/ Agent Central Host Computer / Agent - Central Host Computer.iso / _SETUP.1 / vcuraccDetail.sql < prev    next >
Text File  |  2000-05-12  |  1KB  |  29 lines
  1. /* 
  2. For a range of det_seq_num determined by vcurstaterange, this view "flattens" the curacc table, grouping by bill and coin type, using the decode function; then sums the types.
  3. */
  4. CREATE OR REPLACE VIEW VCURACCDETAIL AS
  5. SELECT vcurstaterange.conversion_num,
  6.     vcurstaterange.open_count,
  7.     vcurstaterange.close_count,
  8.     vcurstaterange.cashbox_glid,
  9.     SUM(CURACC.currency_val) probedcash,
  10.     SUM(DECODE(currency_type, 204, currency_val, 0)) cents_$,
  11.     SUM(DECODE(currency_type, 203, currency_val, 0)) nickels_$,
  12.     SUM(DECODE(currency_type, 202, currency_val, 0)) dimes_$,
  13.     SUM(DECODE(currency_type, 201, currency_val, 0)) quarters_$,
  14.     SUM(DECODE(currency_type, 205, currency_val, 0)) halfs_$,
  15.     SUM(DECODE(currency_type, 206, currency_val, 0)) sbas_$,
  16.     SUM(DECODE(currency_type, 250, 1, 0)) sm_tokens,
  17.     SUM(DECODE(currency_type, 251, 1, 0)) lg_tokens,
  18.     SUM(DECODE(currency_type, 301, currency_val, 0)) ones_$,
  19.     SUM(DECODE(currency_type, 302, currency_val, 0)) fives_$,
  20.     SUM(DECODE(currency_type, 303, currency_val, 0)) tens_$,
  21.     SUM(DECODE(currency_type, 304, currency_val, 0)) twenties_$
  22. FROM vcurstaterange, CURACC
  23. WHERE CURACC.det_seq_num BETWEEN open_count AND close_count
  24. GROUP BY conversion_num,
  25.     open_count,
  26.     close_count,
  27.     vcurstaterange.cashbox_glid;
  28.  
  29.